- frmCreditorsAndDebtorsReport.vb
- project /
1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3
4 Public Class frmCreditorsAndDebtorsReport
5
6 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
7 Me.Close()
8 End Sub
9
10 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
11 Cursor = Cursors.Default
12 Timer1.Enabled = False
13 End Sub
14
15 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnCreditors.Click
16 Try
17 Cursor = Cursors.WaitCursor
18 Timer1.Enabled = True
19 con = New SqlConnection(cs)
20 con.Open()
21 cmd = New SqlCommand("SELECT Supplier.SupplierID,Supplier.Name,Supplier.City,Supplier.ContactNo,Sum(Stock.PaymentDue) from Supplier,Stock where Supplier.ID=Stock.SupplierID group by Supplier.SupplierID,Supplier.Name,Supplier.City,Supplier.ContactNo having (Sum(PaymentDue) > 0) order by Supplier.Name", con)
22 adp = New SqlDataAdapter(cmd)
23 dtable = New DataTable()
24 adp.Fill(dtable)
25 con.Close()
26 ds = New DataSet()
27 ds.Tables.Add(dtable)
28 ds.WriteXmlSchema("Creditors.xml")
29 Dim rpt As New rptCreditors
30 rpt.SetDataSource(ds)
31 rpt.SetParameterValue("p1", Today)
32 frmReport.CrystalReportViewer1.ReportSource = rpt
33 frmReport.ShowDialog()
34 Catch ex As Exception
35 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
36 End Try
37 End Sub
38
39 Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles btnDebtors.Click
40 Try
41 Cursor = Cursors.WaitCursor
42 Timer1.Enabled = True
43 con = New SqlConnection(cs)
44 con.Open()
45 cmd = New SqlCommand("Select CustomerID,Name,City,ContactNo,Sum(Balance) as [Balance] From (Select Customer.CustomerID,Customer.Name,Customer.City,Customer.ContactNo,Sum(Balance) as [Balance] from Customer,InvoiceInfo where Customer.ID=InvoiceInfo.CustomerID group by Customer.CustomerID,Customer.Name,Customer.City,Customer.ContactNo having (Sum(Balance) > 0) Union All Select Customer.CustomerID,Customer.Name,Customer.City,Customer.ContactNo,Sum(Balance) as [Balance] from Customer,InvoiceInfo1,Service where Customer.ID=Service.CustomerID and Service.S_ID=InvoiceInfo1.ServiceID group by Customer.CustomerID,Customer.Name,Customer.City,Customer.ContactNo having (Sum(Balance) > 0)) As Customer Group By CustomerID,Name,City,ContactNo", con)
46 adp = New SqlDataAdapter(cmd)
47 dtable = New DataTable()
48 adp.Fill(dtable)
49 con.Close()
50 ds = New DataSet()
51 ds.Tables.Add(dtable)
52 ds.WriteXmlSchema("Debtors.xml")
53 Dim rpt As New rptDebtors
54 rpt.SetDataSource(ds)
55 rpt.SetParameterValue("p1", Today)
56 frmReport.CrystalReportViewer1.ReportSource = rpt
57 frmReport.ShowDialog()
58 Catch ex As Exception
59 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
60 End Try
61 End Sub
62
63 Private Sub frmStockInAndOutReport_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
64
65 End Sub
66 End Class
